Copyright: Copyright © 1995-97 ATTAC Consulting Group
Citation: Copyright © 1995-97 ATTAC Consulting Group


On the Fly PrintingyesyesyesyesTRUEnono&AboutC&loseC&opy&Printnoprinteryes07/09/97


Table of Contents

Version 8.0 for Access 95 and Access 97

Printing in Access 7 & 8--Introduction

Using/Calling the Functions

What's new in This Release of "On the Fly Printing"

Other Access Products

Registration Information

Copyright © 1995-7, ATTAC Consulting Group, all rights reserved

e-mail:  75323.2112@compuserve.com

web:  http://ourworld.compuserve.com/homepages/attac-cg/acgsoft.htm

 No specific warranty is provided or implied as included in this software.  This is not freeware.  Registration is required for use of the functions in any application, personal or distributed.  This software upon registration is licensed solely to the registered user; developer and distribution licenses are available.  No redistribution of this file or printer8.mdb or any portion thereof is permitted without the express written consent of ATTAC Consulting Group.


Help file produced by  HELLLP! v2.3a , a product of Guy Software, on 9/7/97 for Unregistered User.

The above table of contents will be automatically completed and will also provide an excellent cross-reference for context strings and topic titles.  You may leave it as your main table of contents for your help file, or you may create your own and cause it to be displayed instead by using the I button on the toolbar.  This page will not be displayed as a topic.  It is given a context string of _._ and a HelpContextID property of 32517, but these are not presented for jump selection.

HINT:  If you do not wish some of your topics to appear in the table of contents as displayed to your users (you may want them ONLY as PopUps), move the lines with their titles and contexts to below this point.  If you do this remember to move the whole line, not part.  As an alternative, you may wish to set up your own table of contents, see Help under The Structure of a Help File.

  Do not delete any codes in the area above the Table of Contents title, they are used internally by HELLLP!

Registration

Windows95 and Access95 Compatability

General Use Issues

Calling the Functions In Visual Basic

Calling the Functions from a Macro

Printing Reports with Filters or SQL Where Clauses

Registration Form


Printer Setup in Access 2.0
On the Fly Printer Setup in Access 7 & 8

Printing Reports in Access

No version of Microsoft Access provides the ability for the user to direct or redirect a report to a selected printer "on the fly" when a print command button is clicked on a form.  Neither Access' Print Action or Print method support selection of a printer when the report is sent to print.

Printers can be selected for reports in two way in Access, 1.) open a report in design mode and change the printer or 2.) preview the report and change the printer selection by pushing the print button on the button bar.  But previewing a report in order to changing the printer setup requires two compiles to print the report:  Once to preview it and once to print.  This slows down printing And when using the runtime version of Access, or when a db is saved as an Access 8 "mde" file, a report can't be opened in design mode.

Microsoft created an Active X OLE control (commdlg.ocx) to support printer selection in Access and VB, but there are a number of limitations with this control.  First using Active X controls incurs additional memory overhead, slowing down Access.  Second calling the printer dialog directly from Access via Visual Basic is faster.  Third the commdlg.ocx is not very flexible, as an example it doesn't allow you to save any of the selected printer settings with the report.

A Better Printing Solution

To make printing reports easier in Access for users we created a code module, which is demonstrated in this sample database to facilitate easy printing and printer selection for any report, simply by clicking a command button on a form.  This code may be called from either a macro or VBA making the code useful to both the beginning, intermediate, or advanced Access developer.

The "On the Fly Printing" code module provides the following functionality in printing Access reports:

1.) Allows a user to select to print an Access report to any printer on the system, including fax and network printers through the standard print dialog, which can be called from a command button on a form, without having to compile the report before printing.

2.) The ability to spool multiple reports to a selected printer, despite different paper sizes, paper bins, and orientation.

3.) The ability to specify a filter for the report, when it is sent to print.

4.) "On the Fly Printing" can be used with secure reports where the user does not have design permissions and with MDE files in Access 97.   (See Using/Calling the Functions for more details.)

How to get a copy of the code for your database or application


"On the Fly" 'Fix' issues from originally published Functions
"On the Fly" Release and Enhancement History

Release 1  to  6.0g

These versions were released between March 1995 and October 1996 for Access 2.x

Release 7.0  to 7.04.5

Versions release between November 1995 and January 1997 for Access 7

Version 8.0

Released February 1997 for Access 7 & 8 databases

Adds a new SetPrintMDE() function to support Access 8 dbs saved as "mde" databases.

Adds the ability to change printer for reports in databases where users don't have permissions for design view of reports, by also using the SetPrintMDE() function. (This works in both Access 7 & 8).

Version 8.1  (This release)

Minor usability enhancements to Shareware and Developer's version.   Added additional functionality to call a print dialog which doesn't contain a printer properties option to the Developer's Version of the code.


Registration
Registration Information

Two levels of license available:

	A Shareware Level license allows use of the code in any application(s) or database(s) with a total combined number of users of 5 or less. It provides both the SetPrint and SetPrintMDE functions  Cost for a Shareware License is $20.00 (US)
	A Developer's Application License is available  for developer's needing to use the code in applications or databases with a combined to 6 or more users.  Developer's Application Licenses allow you to use the code in an unlimited number of databases, for any number of users.  Cost for a Developer's Application License is $95.00 (US)
	Added Functionality with Developer's License:  In addition to providing unlimited use in any number of applications and with any number of users, the Developer's Application License includes an expanded version of the code which provides enhanced capability to present a print dialog to the user which doesn't display the printer properties button.  This is extra functionality may be important when using an MDE database or when reports are secured, and user's can't change the page layout or other properties of the report.

Register via CompuServe (Shareware Licenses only):

Register in the Shareware Forum: GO "SWReg"

Shareware program ID/registration # = 7535

We will e-mail you the code upon receipt of registration.

How to Register by Mail (Shareware and Developer's licenses):

1.)  Fill out the Registration Form included in this help file:

2.)  Payment for either level of license may be made by bank check, international money order, or traveler's checks.  We don't currently accept credit cards but we're working on it.

3.)  Send the registration form and payment to ATTAC Consulting Group.  Upon receipt of Registration fees for either a Shareware or Developer's license we will e-mail the code to you.  You may make payment in either of the two following ways.

Registration fees may be mailed to:

ATTAC Consulting Group

2869 Baylis Drive

Ann Arbor, MI  48108 USA

Be sure to include an e-mail address, and version of Access you use so we can send the code.


Using/Calling the Functions

General Use Issues

Calling the Functions using Visual Basic

Calling the Functions Using a Macro

Printing Reports with Filters or SQL Where Clauses


Window 95 and Access95
Compatability

Windows 95 and Windows NT

"On the Fly" Printing in Access 7 & 8 has been fully tested with Access 95 (version 7) and 97 (Version 8) operating under both Windows 95 and NT, and is compatible with either operating system.  The only difference in functionality is that Printing to File is not supported under Windows NT.


Other Access Products

Visit us on the web to download samples of other ATTAC Consulting Group products for

MS Access at http://ourworld.compuserve.com/homepages/attac-cg/acgsoft.htm.

On the Fly Printing for Access 2.x

Similar modules are also available for use under the 16 bit Access 2.x.

Remote Connection Manager for Access 95 & 97

Remote Connection Manager is an Add In/Code Library available for Access 95 or Access 97 which provides developer's a method in Access to initiate and monitor dialup networking connections to the internet, or corporate networks for sending e-mail, initiating replication, connecting to corporate intranets, ftp, or world wide web.  Contains a built in interface which can be used, and exposes its main functions so that developers can build their own user interfaces right into their applications.

"Print to File Manager" Code Library and Add In

Print to File Manager is a Code Library and Add In for Access 2, 7 & 8.  This code library allows developers to add functionality to their databases which will print either a single report, or a batch of reports to pre-specified printers and files, without user intervention to enter file names or printers.  This Add In is great for creating overnight print to file batch jobs, or for use with creating postscript files to generate Adobe PDF files of reports to facilitate e-mailing from Access applications, and many other uses.

Open and Save As Common Dialogs

Our Common Dialog modules, is easily used for calling the File Open and File Save As dialogs.  This single set of modules can be used with any version of Access including version 2.x and Access 95 and 97 which allows application portability for conversion without code changes.  It runs under Window 3.1x, Windows 95 or Windows NT.  The modules provide great flexibility for the developer by allowing custom filters for any major file type, custom dialog titles, and error checking for changed file types.

A major advantage of our common dialog modules is that when run in Access 2.x on either Win 95 or Windows NT, they provide Long File Name support for an Access 2 application, such as the dialog illustrated below (and can return a short "dos 8.3" file name from the long name chosen for compatibility with Access 2.)


Printing Reports with Filters or SQL Where Clauses

Many Access developer's use the "OpenReport "My Report", A_Print, [Filtername],[SQL WHERE clause]" to print reports based on specific filters, or to limit data as specified using the SQL where clause.  There are three common ways to implement filters in "On the Fly Printing".

1.) To accomplish using a filter or where clause when printing a report using either SetPrint() or SetPrintMDE(), the developer can create a separate query for the report which incorporates the specific filter desired.  This query would be separate from and in addition to the report's basic underlying query.  Neither SetPrint() or SetPrintMDE() support simple SQL WHERE clauses; rather such WHERE clauses must be incorporated into a full query.

To implement a filter on the report when printing, both SetPrint() and SetPrintMDE() allow for an optional parameter to be specified as their last parameter when called.  This parameter would be the name of the query which contains the filter, or incorporates the SQL WHERE clause to be used for printing the report.  The following illustrates how this would be added to a standard call.

  If a filter is desired when printing the report, specify the name of the query which incorporates the filter as the last parameter in the call as a string.  So that a sample call for a single report would now look like:

returnval = SetPrint(Me.hwnd,"MyReport",-1,1,-1,"MyFilterQuery")

  Because this filter is an optional parameter, it need not be added or specified as null when no specific or unique query is to be used with the report other than the report's standard record source, it may simply be omitted.  (See Using/Calling the Functions.)

2.) Alternately if the report is based on input from controls on a report, the standard underlying query for the report can reference controls on the form for input for data limitation, such as "Forms!MyForm!MyControl".  If the report is used with multiple forms, then you can specify Screen.ActiveForm!MyControl as the parameter, and use either commonly named controls on each form which calls the report, or use dummy hidden controls with specified values which limit the report's underlying query for the specific purpose used on the particular Form.  (Note if you sometimes limit the report and other times print all records, then your parameters in the query should use the verb "Like" and on the form which prints all records simply use a "*" as the parameter).  This style of parameterizing a report gives the user a great deal of flexibility over the report output by using form driven user input controls, rather than hard coding the parameters in Visual Basic using WHERE clauses where the user can not change them.

3.) A third option uses temporary data storage tables along with various append queries to store query results and then have the report based on the table instead of a query; deleting all the rows in the table when you are done printing a specific report.  This option runs quite quickly and allows you to set all your parameters by code just like a standard [where] clause except that you are specifying them for an append query.


General Use Issues

Set Reports to the Default Printer prior to distribution:

When distributing an application, or moving an application which includes the "On the Fly" modules from one PC to another, all reports should have their printer set and saved using the Default Printer on the design PC.  Set the reports to the default printer for the following reason:

If the computer the application is installed on does not have the same printers available which the application was designed on, and the reports are set to a non-default printer then the user may be confronted with the following Access standard dialog box the first time a report is sent to print:

Setting all reports to the Default Printer will eliminate this dialog from ever popping up.

If for some reason a report is not saved with the default printer and the dialog above pops up the user may simply click OK (or change the printer using setup), and the print dialog will initialize normally, however, this may seem confusing to the user.  (Note:  This same dialog would appear for mobile users which don't have access to a network or their usual local printer if the report is not set for the default printer; Windows 95 users can work around this by setting a non-available network printer to "Work Offline" status in the Printers folder.)

Printing to File

Print to File operations are currently supported when using the SetPrint() or SetPrintMDE() function under Windows 95 only.  To print to file, simply click the check box on the Print Dialog.  Printing to File may also be accomplished by setting up a second copy of any printer and setting the output port to "File".  Then simply select that printer when printing a report.  (Note:  fax printer devices including WinFax Pro, (all versions), and Microsoft Fax, don't support print to file operations.)


Calling the Functions In Visual Basic

General:

"On The Fly Printing" exposes two functions which may be used to setup printers and send reports to print.  The two available functions are SetPrint(), or SetPrintMDE().

The SetPrint() function provides the greatest functionality, allowing not only selection of the printer, but also setting of report properties and the ability to save any printer settings the user selects in the report itself for future use.  Use of SetPrint() requires that user's have full design permission for all reports they will print.  The SetPrint() function works with the runtime version of Access even though there is no "visible" design mode for the user, provided the user still has permissions to enter design mode in the core database itself.

The SetPrintMDE() function must be used if the database/application's reports will be secured, (i.e. users have no design permissions,) or if the application will be converted to an MDE file, where there is no design mode capability.  SetPrintMDE() doesn't allow the changing of a printer's properties when a report is sent to print, and can not save any new printer settings with the report.

	Shareware version vs. Developer's version of SetPrintMDE():  Although user's can't change the printer's properties when calling SetPrintMDE(), under the shareware version they are still presented with a print dialog which has a properties button available.  In the Developer's version, the developer can choose to present a print dialog either with or without a printer properties button.

Call Prototypes:

The calls to each function are virtually identical for the Shareware Version:

Result = SetPrint(hWnd, ReportName, FirstReportFlag, DefaultCopies, SaveInit, [RecSource/Filter])

Result = SetPrintMDE(hWnd, ReportName, FirstReportFlag, DefaultCopies, SaveInit, [RecSource/Filter])

Parameters:

Parameter	Data Required	Data Type
hWnd	Window handle to the Form which contains the print command button calling the function. (e.g. Me.hwnd  or  use 0& if calling from a menu or toolbar.)	Long integer
ReportName	Name of the Report to be printed	String
FirstReportFlag	-1 for single or first in a series of reports which will use the same printer; 0 for second or greater reports.  By setting 0 in this parameter for a second or greater report, the report will be printed using the same printer as the user selected for the first report.	Boolean,   -1 or 0 (True or False)
DefaultCopies	Default number of copies to print, user may override.	Integer>0
SaveInit	Save the report after printing with the initial (current) printer settings (-1) or with the User selected settings (0) (Note: this parameter is required in SetPrintMDE() but it is ignored, all printer settings revert after printing to the Windows default.)	Boolean,   -1 or 0 (True or False)
RecSource (Optional)	The name of a save query which incorporates the filter you want to place on the report's standard query or table.	String
NoPropDialog (Optional) SetPrintMDE ONLY	(Developer's Edition Only) Specify whether the print dialog is to initialize with or without a printer properties button.	Boolean,   -1 or 0 (True or False)

A standard call to print a single report would be:

returnval = SetPrint(Me.hwnd,"MyReport",-1,1,-1)

To print a second or greater report in a series of reports would be:

returnval = SetPrint(Me.hwnd,"MyReport",0,1,-1)

(See Also Printing Reports with Filters or SQL Where Clauses for information on how to specify optional filters and SQL where clauses for a report.)

Return Value

Both SetPrint() and SetPrintMDE() return-1 if the functions succeed and 0 if the user cancels the print action or the functions fail for another reason, such as corruption or deletion of a report.


Calling the Functions from a Macro

General:

"On The Fly Printing" exposes two functions which may be used to setup printers and send reports to print.  The two available functions are SetPrint(), or SetPrintMDE().

The SetPrint() function provides the greatest functionality, allowing not only selection of the printer, but also setting of report properties and the ability to save any printer settings the user selects in the report itself for future use.  Use of SetPrint() requires that user's have full design permission for all reports they will print.  The SetPrint() function works with the runtime version of Access even though there is no "visible" design mode for the user, provided the user still has permissions to enter design mode in the core database itself.

The SetPrintMDE() function must be used if the database/application's reports will be secured, (i.e. users have no design permissions,) or if the application will be converted to an MDE file, where there is no design mode capability.  SetPrintMDE() doesn't allow the changing of a printer's properties when a report is sent to print, and can not save any new printer settings with the report.

The calls to each function are identical for the Shareware Version, simply replace SetPrint in the following example with SetPrintMDE:

Result = SetPrint(hWnd, ReportName, FirstReportFlag, DefaultCopies, SaveInit, [RecSource/Filter])

To review all the function's parameters see the Calling the Functions In Visual Basic section.

Selecting a Printer and Printing a Single Report using a Macro

The following example shows how to print a select a printer and print a single report using the SetPrint() function.  It assumes you want one copy of the report printed, and you don't want to have the report saved with the new printer settings after printing, but rather to retain the original printer settings of the report.

Steps to create the macro:

1.)	Import the On the Fly code module into your database by opening a new module, selecting insert file and choosing the OTF code file.
2.)	Create a new Macro
3.)	Show the Macro Names column and give the Macro a name.
4.)	For an Action choose the "RunCode" option.
5.)	In the space provided to name the function type the following:   SetPrint(Forms!MyForm.hwnd,"MyReportName",-1 , 1, -1) Where MyForm = the name of the form you are placing the command button on, and MyReportName = the name of the report you want to print.
6.)	Create a command button on your form and enter the name of the macro you just created.

Running an Action in the Macro only if printing is successful:

If you want to take an action after the reports are printed, such as running a update query to record that the reports have been printed, call the functions in the following manner:

In the Conditions column of the macro type:

SetPrint(Forms!MyForm.hwnd,"MyReport",-1,1,-1) = -1

(-1 is the result if printing was successful).

In the Action Column Select the action you want to take after printing such as running a query or displaying a message.

Printing Multiple Reports at One Time:

To spool a number of reports (i.e. print them all one after another to the same printer,) call the function for the first report in the same manner as you would if you want to take an Action as noted above.  Leave the Action column blank.

In the line below the first Setprint() call, place an ellipsis (...) in the conditions column, and then in the Action column choose Runcode.  In the space provided to name the function then type SetPrint(Forms!MyForm.hwnd,"MySecondReportName",0,1,-1).  Follow the same steps for the third, forth etc. reports.


Registration Form

Please check which version of Access you will be using with "On the Fly Printing":

Access 95  ___________    Access 97  ___________

Print this form by clicking the Print Button

Name (Required): __________________________________	Phone # (Required for Developer) __________________
Company (Required):_______________________________________

Address (Required)________________________________________________________________________________

                                  Street                            City                         State/Province         Postal Code          Country

E-Mail Address: (Required)_______________________________________________

License Level Desired:

Shareware _____ ($20.00 US)	Developer Application License ______ ($95 US)
Information we'd like to know if you'd share it with us:

 How did you hear about On the Fly Printing:  OnLine Service ____  Word of Mouth ____ Review ____

Internet  _____ (Please provide the site URL: _____________________________________)

Operating System:  Windows 95 ____  Windows NT _____  Other ______

Enclose a bank or corporate check the amount specified above and mail to:

ATTAC Consulting Group

2869 Baylis Drive

Ann Arbor, MI  48108  USA

e-mail: 75323.2112@Compuserve.com